/**
 *
 */
package com.test.util;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;

import com.test.domain.User;

/**
 * @author Administrator
 * @date 2018年5月27日 下午2:39:46
 * 
 */
public class JdbcUtil {
	
	private static final String url = "jdbc:mysql://127.0.0.1:3306/web01";
	
	private static final String userName = "root";
	
	private static final String password = "123456";
	
	static{
		//1. 加载连接mysql的驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 获取数据库连接
	 * @return 数据库连接
	 */
	private static Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url,userName,password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
		
	}
	
	/**
	 * 释放资源
	 * @param stateMent
	 * @param conn
	 */
	private static void release(Statement stateMent,Connection conn) {
		try {
			//6.关闭statement
			if(stateMent !=null)stateMent.close();
			//7.关闭连接
			if(conn !=null )conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 执行新增，修改，删除，操作
	 * @param sql
	 */
	public static void excute(String sql  ) {
		Connection conn = null;
		Statement stateMent = null;
		try {
			 conn = getConnection();
			//3.通过连接获取statement
			 stateMent = conn.createStatement();
			//4.用statement 执行sql语句
			stateMent.execute(sql);
			//5.处理查询结果
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			release(stateMent,conn);
		}
	}
	
	/**
	 * 执行查询
	 * @param sql
	 * @return
	 */
	public static List query(String sql , String name) {
		List<Map> result = new ArrayList<>();
		Connection conn = null;
		PreparedStatement preStateMent = null;
		try {
			 conn = getConnection();
			//3.通过连接获取statement
//			 stateMent = conn.createStatement();
			 //select * from user where name like '?' and password like '?'
			 preStateMent =  conn.prepareStatement(sql);
			 preStateMent.setString(1, name);
			 ResultSet resultSet = preStateMent.executeQuery();
			 ResultSetMetaData resultMetaData =  resultSet.getMetaData();
			 int cols = resultMetaData.getColumnCount();
			//5.处理查询结果
			while(resultSet.next()) {
				Map tempMap  = new HashMap<>();
				for(int i = 1; i <= cols; i++ ) {
					tempMap.put(resultMetaData.getColumnLabel(i), resultSet.getString(i));
				}
				//把转换后的User对象添加到返回值的结果集中
				result.add(tempMap);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			release(preStateMent, conn);
		}
		return result;
	
	}
}
